<?php

include_once("config/config.inc.php");
include_once("acctg/class.baseobject.php");
include_once("acctg/class.shift.php");
include_once("acctg/class.salesreceiptsreport.php");


function getStartTime()
{
	$sql = "select datetime from `shift-transactions` where shift = 'start' order by datetime desc";
		
	$res = mysql_query($sql);
	while(list($time) = mysql_fetch_row($res))
	{
		return $time;
	}	
}

function getThisShift() {
	$time = date("H");

	if($time == '11' || $time == '00') {
		return 3;
	} elseif( $time == '07' || $time == '08') {
		return 1;
	} elseif( $time == '15' || $time == '16') {
		return 2;
	}
	
}


function getReport($cashdeclaration,$l)
{
	//$end = date("Y-m-d H:i:s");
	$startshift = getStartTime();

	$shiftno = getThisShift();

	$shift= new shift(array('date'=>date('Y-m-d',strtotime($startshift)),'shiftno'=>$shiftno));
	$shift->getShiftDuration();
	
	$end = $shift->shiftEnd;
	//$startshift = $shift->shiftStart;
	
	
	$report = new salesreceiptsreport( array('start'=>$startshift, 'end'=>$end) );
	if($l) {
		$ret = "SHOGUN 2: SHIFT END REPORT";
	}else{
		$ret = "SHOGUN 2: MID SHIFT REPORT";
	}
	$ret .= "\n";
	$ret .= date("l F d, Y g:i:s A");
	$ret .= "\n";
	$ret .= "\n";
	$ret .= getPrintSalesTransaction($cashdeclaration,$startshift,$end,$l);
	$ret .= "\n".getPrintableAmountRoomTotal($startshift,$end);
	$ret .= "\n".getPrintableDiscountRoomTotal($startshift,$end);
	//$ret .= "\n\n".getSalesInTransit($startshift,$end,$lobbyid);
	$ret.= "\n\n\n".getPrintReservations($startshift,$end);
	$ret.= "\n\n\n".getPrintSecurityDeposits($startshift,$end);
	$ret .= "\n\n".$report->getPrintableSalesreceipts();
	$ret .= "\n\n".getRechitList();
	return $ret;
}

function getPrintReservations($startshift,$end)
{
	$sql = "select transaction_date, reservation_code, amount_deposit, amount_claimed from reservation_transactions
			where transaction_date >= '$startshift' and transaction_date <='$end' 
		";
	$res = mysql_query($sql);
	while(list($date,$code,$dep,$claim)=mysql_fetch_row($res)) {
		if($dep != '0.00') $deposits .="\n$date\t$code\t$dep";
		if($claim != '0.00') $claims .="\n$date\t$code\t$claim";
	}
	$ret = "=== RESERVATIONS ===\n\n";
	$ret.= "Deposits:\n";
	$ret.=$deposits;
	$ret.= "\n\nClaimed:\n";
	$ret.=$claims;
	return $ret;
}

function getPrintSecurityDeposits($startshift,$end)
{
	$sql = "select date_endorsed, amount, remarks from security_receivables
			where date_endorsed >= '$startshift' and date_endorsed <='$end' 
		";
	$res = mysql_query($sql);
	while(list($date,$dep,$remarks)=mysql_fetch_row($res)) {
		if($dep != '0.00') $deposits .="\n$date\t$dep\t$remarks";
	}
	$ret = "SECURITY DEPOSITS\n\n";
	$ret.= "Deposits:\n";
	$ret.=$deposits;
	$sql = "select date_remitted, amount, remarks from security_receivables
			where date_remitted >= '$startshift' and date_remitted <='$end' 
		";
	$res = mysql_query($sql);
	while(list($date,$dep,$remarks)=mysql_fetch_row($res)) {
		if($dep != '0.00') $claimed .="\n$date\t$dep\t$remarks";
	}
	$ret.= "Claimed:\n";
	$ret.=$claims;
	return $ret;
}

function getPrintSalesTransaction($cashdeclaration,$startshift,$end,$l)
{
	$lsql = "select settings_value from settings where id = '3'";
	$lres = mysql_query($lsql);
	list($lobbyid)=mysql_fetch_row($lres);

	$rooms = getCheckoutRoomDeposits($startshift,$end) + getCheckoutExtensions($startshift,$end);
	$foods = getCheckoutFoodTotalAmount($startshift,$end,$lobbyid);
	$beers = getCheckoutBeerTotalAmount($startshift,$end,$lobbyid);
	$miscs = getCheckoutMiscTotalAmount($startshift,$end,$lobbyid);	
	$adjustments = getCheckoutRoomAdjustmentTotal($startshift,$end);
	$grosssales = $rooms+$foods+$miscs+$beers+$adjustments;


	$drooms = getCheckoutDiscountRoomTotalAmount($startshift,$end);
	$dfoods = 0;
	$dmiscs = 0;

	$deposit = getTotalDeposit($startshift,$end,$lobbyid);
	$refund = getTotalRefund($startshift,$end,$lobbyid);

	$taccountability = (($grosssales-($drooms+$dfoods+$dmiscs))+$deposit)-abs($refund);
	$card = getCheckoutPaidByCard($startshift,$end);
	$safekeep = getSafekeepAmountByTimeframe($startshift,$end);
	
	$currentcash = getCurrentCash($l);
	$totalcashdeclared = $card+$safekeep+$cashdeclaration;
	$overshortages =  $cashdeclaration-$currentcash;

	$ret .= "SALES TRANSACTION";
	$ret .= "\n";	
	$ret .= "Room Sales Order\t\t\t";	
	$ret .= "$rooms";
	$ret .= "\n";
	$ret .= "FnB Sales Order\t\t\t";	
	$ret .= "$foods";
	$ret .= "\n";
	$ret .= "Beer Sales Order\t\t\t";	
	$ret .= "$beers";
	$ret .= "\n";
	$ret .= "Misc Sales Order\t\t\t";	
	$ret .= "$miscs";
	$ret .= "\n";	
	$ret .= "Room Sales Adjustment\t\t\t";	
	$ret .= "$adjustments";
	$ret .= "\n";	
	$ret .= "GROSS SALES\t\t\t";	
	$ret .= "$grosssales";
	$ret .= "\n";
	$ret .= "\n";
	$ret .= "DISCOUNT";	
	$ret .= "\n";	
	$ret .= "Room\t\t\t\t";	
	$ret .= "$drooms";
	$ret .= "\n";	
	$ret .= "FnB\t\t\t\t";	
	$ret .= "$dfoods";
	$ret .= "\n";	
	$ret .= "Misc\t\t\t\t";	
	$ret .= "$dmiscs";
	$ret .= "\n";	
	$ret .= "\n";
	$ret .= "DEPOSIT\t\t\t\t";	
	$ret .= "$deposit";
	$ret .= "\n";
	$ret .= "REFUND\t\t\t\t";	
	$ret .= "$refund";
	$ret .= "\n";	
	$ret .= "\n";
	$ret .= "TOTAL ACCOUNTABILITY\t\t\t";	
	$ret .= "$taccountability";
	$ret .= "\n";	
	$ret .= "\n";
	$ret .= "CASH DECLARATION\t\t\t";	
	$ret .= "$cashdeclaration";
	$ret .= "\n";
	$ret .= "CARD\t\t\t\t";	
	$ret .= "$card";
	$ret .= "\n";
	$ret .= "FUNDS SAFEKEEP\t\t\t";	
	$ret .= "$safekeep";
	$ret .= "\n";
	$ret .= "TOTAL CASH DECLARED\t\t\t";	
	$ret .= "$totalcashdeclared";
	$ret .= "\n";
	$ret .= "\n";
	$ret .= "TOTAL CASH ON SYSTEM\t\t\t";	
	$ret .= "$currentcash";
	$ret .= "\n";
	$ret .= "OVER/SHORTAGES \t\t\t";	
	$ret .= "$overshortages";
	$ret .= "\n";

	return $ret;
}

function getSalesTransaction($cashdeclaration,$startshift,$end)
{
	$lsql = "select settings_value from settings where id = '3'";
	$lres = mysql_query($lsql);
	list($lobbyid)=mysql_fetch_row($lres);

	$cashdeclaration = getCashDeclaration($end);
	$rooms = getCheckoutRoomDeposits($startshift,$end) + getCheckoutExtensions($startshift,$end);
	$foods = getCheckoutFoodTotalAmount($startshift,$end,$lobbyid);
	$beers = getCheckoutBeerTotalAmount($startshift,$end,$lobbyid);
	$miscs = getCheckoutMiscTotalAmount($startshift,$end,$lobbyid);	
	$adjustments = getCheckoutRoomAdjustmentTotal($startshift,$end);
	$grosssales = $rooms+$foods+$miscs+$adjustments+$beers;


	$drooms = getCheckoutDiscountRoomTotalAmount($startshift,$end);
	$dfoods = 0;
	$dmiscs = 0;

	$deposit = getTotalDeposit($startshift,$end,$lobbyid);

	$taccountability = $grosssales-($drooms+$dfoods+$dmiscs);
	$card = getCheckoutPaidByCard($startshift,$end);
	$safekeep =  getSafekeepAmountByTimeframe($startshift,$end);
	
	$totalcashdeclared = $card+$safekeep+$cashdeclaration;
	$ret .= "<table>";
	$ret .= "<tr>";
	$ret .= "<td>SALES TRANSACTION</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Room Sales Order</td>";	
	$ret .= "<td>$rooms</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>FnB Sales Order</td>";	
	$ret .= "<td>$foods</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Beer Sales Order</td>";	
	$ret .= "<td>$beers</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Misc Sales Order</td>";	
	$ret .= "<td>$miscs</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Room Sales Adjustment</td>";	
	$ret .= "<td>$adjustments</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>GROSS SALES</td>";	
	$ret .= "<td>$grosssales</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>DISCOUNT</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Room</td>";	
	$ret .= "<td>$drooms</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>FnB</td>";	
	$ret .= "<td>$dfoods</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>Misc</td>";	
	$ret .= "<td>$dmiscs</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>TOTAL ACCOUNTABILITY</td>";	
	$ret .= "<td>$taccountability</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>&nbsp;</td>";	
	$ret .= "<td>&nbsp;</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>CASH DECLARATION</td>";	
	$ret .= "<td>$cashdeclaration</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>CARD</td>";	
	$ret .= "<td>$card</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>FUNDS SAFEKEEP</td>";	
	$ret .= "<td>$safekeep</td>";
	$ret .= "</tr>";
	$ret .= "<tr>";
	$ret .= "<td>TOTAL CASH DECLARED</td>";	
	$ret .= "<td>$totalcashdeclared</td>";
	$ret .= "</tr>";
	$ret .= "</table>";
	
	$ret.=getSalesInTransit($lobbyid);
	return $ret;
}

function getCheckoutRoomDeposits($startdt,$end)
{	
	$total = 0;
	$sql = "select unit_cost,qty from room_sales a, occupancy b where a.status in ('Paid')
	and a.occupancy_id = b.occupancy_id
	and a.update_date >= '$startdt'
	and a.update_date <= '$end'
	and a.item_id = '15'";
	$res = mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($unit_cost*$qty);
	}
	return $total;
}

function getCheckoutExtensions($startdt,$end)
{
	$total = 0;
	$sql = "select unit_cost,qty from room_sales a,occupancy b
		where a.status in ('Paid')
		and a.occupancy_id = b.occupancy_id
		and a.update_date  >= '$startdt'
		and a.update_date <= '$end'
		and a.item_id = '16'";
	$res = mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($unit_cost*$qty);
	}

	return $total;	
}

function getCheckoutFoodTotalAmount($startdt,$end,$lobbyid)
{
	$_sql = "select unit_cost*qty from fnb_sales a, occupancy b
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'
			and a.update_date >= '$startdt'
			and a.update_date <= '$end'
			and a.occupancy_id = b.occupancy_id";
	$_res = mysql_query($_sql) or die(mysql_error());
	$fnbtot =0;
	while(list($fnbamount)=mysql_fetch_row($_res))
	{
		$fnbtot = $fnbtot + $fnbamount;
	}

	$_sql = "select unit_cost*qty from fnb_sales a
			where status in ('Paid')
			and category_id <> '21'
			and category_id <> '17'
			and update_date >= '$startdt'
			and update_date <= '$end'
			and occupancy_id = '$lobbyid'";
	$_res = mysql_query($_sql) or die(mysql_error());
	while(list($fnblobbyamount)=mysql_fetch_row($_res))
	{
		$fnblobbytot = $fnblobbytot + $fnblobbyamount;
	}
	
	return $fnbtot+$fnblobbytot;
}

function getCheckoutBeerTotalAmount($startdt,$end,$lobbyid)
{
	$_sql = "select unit_cost*qty from fnb_sales a, occupancy b 
			where a.status in ('Paid')
			and (a.category_id = '21'
			or a.category_id = '17')
			and a.update_date >= '$startdt'
			and a.update_date <= '$end'
			and a.occupancy_id = b.occupancy_id";
	$_res = mysql_query($_sql);
	$beertot = 0;
	while(list($beeramount)=mysql_fetch_row($_res))
	{
		$beertot = $beertot + $beeramount;
	}
	
	$_sql = "select unit_cost*qty from fnb_sales 
		where status in ('Paid')
		and (category_id = '21'
		or category_id = '17')
		and update_date >= '$startdt'
		and update_date <= '$end'
		and occupancy_id = '$lobbyid'";
	$_res = mysql_query($_sql);
	while(list($beerlobbyamount)=mysql_fetch_row($_res))
	{
		$beerlobbytot = $beerlobbytot + $beerlobbyamount;
	}

	return $beertot+$beerlobbytot;
}

function getCheckoutMiscTotalAmount($startdt,$end,$lobbyid)
{
	$_sql = "select unit_cost*qty from room_sales a, occupancy b
		where a.status in ('Paid')
		and b.occupancy_id = a.occupancy_id
		and a.category_id <> '3'		
		and a.update_date >= '$startdt'
		and a.update_date <= '$end'";
			
	$_res = mysql_query($_sql);
	$misctot = 0;
	while(list($miscamount)=mysql_fetch_row($_res))
	{
		$misctot = $misctot + $miscamount;
	}
	$_sql = "select unit_cost*qty from room_sales 
		where status in ('Paid')
		and category_id <> '3'
		and occupancy_id = '$lobbyid'
		and update_date >= '$startdt' 
		and update_date <= '$end' ";
			
	$_res = mysql_query($_sql);
	while(list($misclobbyamount)=mysql_fetch_row($_res))
	{
		$misclobbytot = $misclobbytot + $misclobbyamount;
	}
	return $misctot+$misclobbytot;
}

function getCheckoutRoomAdjustmentTotal($startdt,$end)
{	
	$total = 0;
	$sql = "select unit_cost,qty from room_sales  a, occupancy b, sales_and_services c,sas_category d
		where a.item_id=c.sas_id
		and b.occupancy_id = a.occupancy_id
		and c.sas_cat_id = d.sas_cat_id
		and c.sas_id = '18'
		and a.update_date >= '".$startdt."'
		and a.update_date <= '".$end."'
		and a.status in ('Paid')";
	$res=mysql_query($sql);
	while(list($unit_cost,$qty)=mysql_fetch_row($res))
	{
		$total = $total + ($qty * $unit_cost);		
	}
	return $total;
}

function getCheckoutDiscountRoomTotalAmount($startdt,$end)
{
	$sql = "select c.discount_given 
	from occupancy a,rooms b, discount_log c
	where a.room_id = b.room_id	
	and a.occupancy_id = c.occupancy_id	
	and c.update_date >= '".$startdt."' 
	and c.update_date  <= '".$end."'";	
	$res = mysql_query($sql);
	$amount = 0;
	while(list($discount_given)=mysql_fetch_row($res))
	{
		$amount += $discount_given;		
	}	
	return -($amount);
}

function getCheckoutPaidByCard($startdt,$end)
{
	$sum = 0;
	$sql = "select sum(amount) from salesreceipts a, occupancy b
	where a.occupancy_id = b.occupancy_id
	and a.receipt_date >= '".$startdt."' 
	and a.receipt_date <= '".$end."'
	and tendertype='Card'";
	$res = mysql_query($sql) or die(mysql_error($sql));
	list($sum)=mysql_fetch_row($res);
	if(!$sum)
	{
		$sum = 0;
	}
	return $sum;
}

function getSafekeepAmountByTimeframe($start, $end)
{
	$ret = 0;
	
	$sql = "select current_amount from safekeep where safekeep_date >= '$start' and safekeep_date <= '$end' 
	order by current_amount desc";


	$res = mysql_query($sql);
	list($ret)=mysql_fetch_row($res);
	if(!$ret)
	{
		$ret = 0;
	}
	return $ret;
}

function getPrintableAmountRoomTotal($startshift,$end)
{
	$sql = "SELECT `room_type_id`, `room_type_name` FROM `room_types` ";
	$res = mysql_query($sql);
	$ret .= "ROOM\t ";
	$ret .= "# OF CHECK-OUT\tAMT";

	while(list($room_type_id,$room_type_name) = mysql_fetch_row($res))
	{
		if($room_type_name == "Executive Suite" || $room_type_name == "Single Bed" || $room_type_name == "Family Room" || $room_type_name == "Mega Suite")
		{
			$t = "\t";
		}
		else
		{
			$t = "\t\t";
		}
		$t .= getNumRoomByTypeID($room_type_id,$startshift,$end)."\t";

		
		$ret .= "\n";		
		$ret .= "$room_type_name $t".getAmountByRoomTypeID($room_type_id,$startshift,$end);
		$i++;
	}
	return $ret;
}

function getAmountByRoomTypeID($roomtypeid,$startdt,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startdt."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$roomtypeid'";
	
	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{		
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where (item_id ='16' 
		or item_id ='15' 
		or item_id = '18') 
		and occupancy_id in ($id) 
		and category_id <> 2
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	if(!$total)
	{
		$total = 0;
	}
	
	return $total;
}

function getNumRoomByTypeID($room_type_id,$startshift,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startshift."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$room_type_id'";
	$res = mysql_query($sql);
	$num = mysql_num_rows($res);
	return $num;
}

function getPrintableDiscountRoomTotal($startshift,$end)
{
	$sql = "SELECT `room_type_id`, `room_type_name` FROM `room_types` ";
	$res = mysql_query($sql);
	$ret .= "\n";
	$ret .= "DISCOUNT ";
	while(list($room_type_id,$room_type_name) = mysql_fetch_row($res))
	{
		if($room_type_name == "Regent" || $room_type_name == "Food" || $room_type_name == "Value")
		{
			$t = "\t\t\t";
		}		
		else if($room_type_name == "Executive Suite")
		{
			$t = "\t";
		}
		else
		{
			$t = "\t\t";
		}
		$ret .= "\n";		
		$ret .= "$room_type_name $t  ".getDiscountByRoomTypeID($room_type_id,$startshift,$end);		
	}
	return $ret;
}

function getDiscountByRoomTypeID($room_type_id,$startdt,$end)
{
	$sql = "select a.occupancy_id from  occupancy a,rooms b
	where a.room_id = b.room_id
	and a.actual_checkout >= '".$startdt."'
	and a.actual_checkout <= '".$end."'
	and b.room_type_id = '$room_type_id'";

	$res = mysql_query($sql) or die(mysql_error().$sql);
	
	while(list($occupancy_id)=mysql_fetch_row($res))
	{
		$id .= "'".$occupancy_id."',";
	}
	$id  = substr_replace($id ,"",-1);
	if($id!="")
	{	
		$_sql = "select unit_cost,qty from room_sales 
		where item_id ='17'  
		and occupancy_id in ($id) 
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$total = $total + ($unit_cost * $qty);
			}	
		}
	}
	
	return $total;



	$res = mysql_query($sql);
	$amount = 0;
	while(list($discount_given)=mysql_fetch_row($res))
	{
		$amount += $discount_given;		
	}	
	if(!$amount)
	{
		$amount = 0;
	}
	return -($amount);
}

function getSalesInTransit($start,$end,$lobbyid)
{
	$sql = "select * from occupancy where actual_checkout = '0000-00-00 00:00:00' and occupancy_id <> '$lobbyid'";
	$res = mysql_query($sql);
	$retval = "SALES IN TRANSIT\n";
	$retval .= "RM NO";
	$retval .= "\tCheckin Time";
	$retval .= "\tRate   Deposit   Order";
	$retval .= "\n";
	while($row = mysql_fetch_array($res))
	{
		$_sql = "select door_name from rooms where room_id ='".$row["room_id"]."'";
		$_res = mysql_query($_sql);
		list($door_name)=mysql_fetch_row($_res);

		$_sql = "select rate_name from rates where rate_id ='".$row["rate_id"]."'";
		$_res = mysql_query($_sql);
		list($rate_name)=mysql_fetch_row($_res);

		$_sql = "select unit_cost,qty from room_sales 
		where (item_id ='16' 
		or item_id ='15' 
		or item_id = '18') 
		and occupancy_id = '".$row["occupancy_id"]."'
		and status in ('Paid')";
		$_res = mysql_query($_sql) or die(mysql_error().$_sql);
		$deposit =0;
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$num = $unit_cost * $qty;
			if($num > 0)
			{
				$deposit = $deposit + ($unit_cost * $qty);
			}	
		}

		$_sql = "select unit_cost,qty from fnb_sales a
		where status in ('Paid') 
		and occupancy_id = '".$row["occupancy_id"]."'
		and a.update_date >= '$start'
		and a.update_date <= '$end'";
		$_res=mysql_query($_sql);
		$order=0;
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$order = $order + ($unit_cost*$qty);
		}


		$_sql = "select unit_cost,qty from room_sales  a, occupancy b, sales_and_services c,sas_category d
		where a.item_id=c.sas_id
		and b.occupancy_id = a.occupancy_id
		and c.sas_cat_id = d.sas_cat_id
		and d.sas_cat_id < 3
		and a.occupancy_id = '".$row["occupancy_id"]."'
		and a.status in ('Paid')";
		
		$_res=mysql_query($_sql);
		while(list($unit_cost,$qty)=mysql_fetch_row($_res))
		{
			$order = $order + ($unit_cost*$qty);
		}

		
		
		
		$retval .= $door_name."    ".date("m/d/Y h:i:s A", strtotime($row["actual_checkin"]))."    ".str_replace("HRS", "", $rate_name)."    ".$deposit."     ".$order;	
		$retval .= "\n";
	}
	

	return $retval;
}

function getTotalDeposit($start,$end,$lobbyid)
{
	//room
	$sql = "select unit_cost,qty from room_sales a, occupancy b 
	where a.status in ('Paid')
	and a.occupancy_id = b.occupancy_id
	and a.occupancy_id <> '$lobbyid'
	and a.update_date >= '$start'
	and a.update_date <= '$end'
	and b.actual_checkout = '0000-00-00 00:00:00'
	and a.item_id = '15'";
	$res = mysql_query($sql);
	while(list($runit_cost,$rqty)=mysql_fetch_row($res))
	{
		$rtotal = $rtotal + ($runit_cost*$rqty);
	}
	
	//food
	$sql = "select unit_cost,qty from fnb_sales a,occupancy b
	where a.occupancy_id = b.occupancy_id
	and a.occupancy_id <> '$lobbyid'
	and a.update_date >= '$start'
	and a.update_date <= '$end'
	and b.actual_checkout = '0000-00-00 00:00:00'
	and a.status in ('Paid') ";	
	$res=mysql_query($sql);
	while(list($funit_cost,$fqty)=mysql_fetch_row($res))
	{
		$ftotal = $ftotal + ($funit_cost*$fqty);
	}
	
	//misc
	$sql = "select unit_cost,qty from room_sales  a, occupancy b
	where b.occupancy_id = a.occupancy_id
	and a.occupancy_id <> '$lobbyid'	
	and a.category_id <> '3'
	and a.update_date >= '$start'
	and a.update_date <= '$end'
	and b.actual_checkout = '0000-00-00 00:00:00'		
	and a.status in ('Paid')";
	$res=mysql_query($sql);
	while(list($munit_cost,$mqty)=mysql_fetch_row($res))
	{
		$mtotal = $mtotal + ($munit_cost*$mqty);
	}

	$total = $rtotal+$ftotal+$mtotal;		
	
	return $total;
}

function getTotalRefund($start,$end,$lobbyid)
{
	$sql = "select sum(amount) from salesreceipts a, occupancy b
	where a.occupancy_id = b.occupancy_id
	and b.occupancy_id <> '$lobbyid'
	and a.receipt_date >= '$start' 
	and a.receipt_date <= '$end'
	and b.actual_checkout = '0000-00-00 00:00:00'
	and a.tendertype='Cash'
	and a.amount like '-%'";
	$res = mysql_query($sql);
	list($sum)=mysql_fetch_row($res);
	return $sum;
}

function getCashDeclaration($end)
{
	$sql = "select amount from `cash_on_hand` where datetime = '$end'";
	$res = mysql_query($sql);
	list($amount)=mysql_fetch_row($res);
	return $amount;
}

function getCurrentCash($l)
{
	
	echo $sql = "select current_amount from `current_cash` order by cc_date desc limit $l,1";
	$res =mysql_query($sql);
	list($ret)=mysql_fetch_row($res);
	return $ret;
}

//////////////////////////////
// rechitlist functions
$now = date("Y-m-d H:i:s");

function getShiftStartTime()
{
	$sql = "select datetime from `shift-transactions` where shift = 'start' order by datetime desc limit 0,1";
	$res = mysql_query($sql);
	list($time) = mysql_fetch_row($res);
	return $time;
}

function getSpecialFloorID() 
	{
		$sql = " select settings_value from settings where settings_name = 'SPECIALFLOORID' ";
		$res = mysql_query($sql) or die($sql);
		
		if(mysql_num_rows($res)){
			$row = mysql_fetch_row($res);
			return $row[0];
		} 
	}
	
 function getSpecialRoomIdList()
	{
		//echo "getSpecialRoomIdList<br>";
		$fid = getSpecialFloorId();
		$sql = " select room_id from rooms where floor_id=$fid";
		
		$res = mysql_query($sql) or die($sql);
		while(list($id)=mysql_fetch_row($res)) {
			$arrSpecialRooms[]=$id;
		}
		return implode(",",$arrSpecialRooms);
	}

function getPreviousShiftEndDate( $occupancy_id, $sales_date)
{
	$sql = " select sales_date from room_sales
			where item_id=15 and occupancy_id='$occupancy_id' and sales_date < '$sales_date'
			order by roomsales_id desc limit 0,1
	";
	$sql = "select datetime from `shift-transactions` where shift = 'start' and datetime <='$sales_date' order by datetime desc limit 0,1
			";
	$res = mysql_query($sql);
	if(mysql_num_rows($res)) {
		$row = mysql_fetch_row($res);
		return $row[0];
	}else{
		return 0;
	}
}
	
function getRechitList() {
	$exceptList = getSpecialRoomIdList();
	$start = getShiftStartTime();
	$end = date('Y-m-d H:i:s', strtotime($start . ' +8 hours'));
	$cutoff = date('Y-m-d H:i:s', strtotime($start . ' -12 hours'));
	$h = date("H");
	if($h<=7||$h<=8)
	{
		$shiftnum=1;
	}
	elseif($h>=15||$h>=16)
	{
		$shiftnum=3;
	}else{
		$shiftnum=2;
	}
	$sql = "
				select distinct e.sales_date, e.roomsales_id,b.door_name, a.occupancy_id,a.actual_checkin,a.actual_checkout,
				b.site_id,b.door_name,b.room_id,c.room_type_id, c.room_type_name,c.rank
				from occupancy a, rooms b, room_types c, rates d , room_sales e
				where 
				(
				
				(e.sales_date >= '$start' and e.sales_date <= '$end' and a.actual_checkin <> e.sales_date and e.item_id=15)
				)
				and a.occupancy_id=e.occupancy_id
				and a.room_id = b.room_id
				and b.room_type_id=c.room_type_id
				and a.rate_id=d.rate_id
				and b.site_id=2
				and e.item_id in (15)
				and a.room_id not in ($exceptList)
				";
	

	$res = mysql_query($sql) or die(mysql_error());
	$ret = "=== RECHIT LIST ===";
	while(list($date, $roomsales_id, $door,$occupancy,)=mysql_fetch_row($res)) {
		$transaction_start=getPreviousShiftEndDate($occupancy,$date);
		$ret.="\n\nRoom No. $door \t Rechit Date: $date\n";
		$ret.="\n" . getFnbRechitSales($occupancy,$transaction_start);
		$ret.="\n" . getRechitMosSales($occupancy,$transaction_start);
	}	
	return $ret;
}

function getRechitMosSales($occupancy,$start)
	{
		//retrieve fnb sales
		$mtotal = 0;
		$sql = " select a.roomsales_id, a.category_id, a.item_id, b.sas_description, a.unit_cost * a.qty, a.update_date,a.order_code
			from room_sales a, sales_and_services b		
			where a.item_id=b.sas_id and 
			a.category_id in (1, 2,4) 
			and a.occupancy_id=$occupancy
			and a.update_date >='$start'
			and a.status  in ('Paid') ";
		$res = mysql_query($sql) or die(mysql_error());
		$ocode = '';
		while(list($rsid, $catid, $itemid, $itemdesc,$cos,$date,$order_code)=mysql_fetch_row($res)) {
			$date = date('m-d H:i',strtotime($date));
			if($ocode != $order_code) {
				$msales .= "\nOrder Code# $order_code\t\t $date\n";
				$ocode=$order_code;
			}
			$msales.= "\n";
			$msales.="\t $itemdesc \t ".number_format($cost,2)."\t";
			$mtotal += $cost;
			
		}
		$mtotal_f =number_format($mtotal,2);
		
		
		$retval = "\n\nMiscellaneous Sales 
		\n $msales
		\n Sub Total:\t\t $mtotal_f\n\n";
		if($mtotal > 0) {
			return $retval;	
		}
	}

function getFnbRechitSales($occupancy,$start)
	{
//retrieve fnb sales
		
		$ftotal = 0;
		$sql = " select a.fnbsales_id, a.category_id,  a.item_id, c.food_category_name, 
				b.fnb_code,b.fnb_name, a.unit_cost, a.qty, 
				(a.unit_cost * a.qty) as totalcost, a.update_date, a.order_code
				from fnb_sales a, fnb b, food_categories c		
				where a.item_id=b.fnb_id and a.category_id=b.food_category_id 
				and a.category_id=c.food_category_id 
				and a.update_date >='$start'
				and a.occupancy_id=$occupancy and a.status  in ('Paid') ";
		$res = mysql_query($sql) or die(mysql_error());
		$ocode='';
		while(list($sid, $catid, $itemid, $catname, $code, $itemdesc, $unitcost, $qty, $ftcost,$date,$order_code)=mysql_fetch_row($res)) {
			$date = date('m-d H:i',strtotime($date));
			if($ocode != $order_code) {
				$fsales .= "\nOrder Code# $order_code \t $date";
				$ocode=$order_code;
			}
			$fsales .= "\n";
			$fsales.="\t\t $itemdesc \t ".number_format($ftcost,2);
			$ftotal += $ftcost;
		}
		$ftotal_f =number_format($ftotal,2);
		
		
		$retval = "\n\nFood and Beverage Sales\n
		\n $fsales
		\t\t Sub Total:\t\t $ftotal_f \n";
		
		if($ftotal > 0) {
			return $retval;	
		}
	}


?>